using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SharpCore.Data;
using SharpCore.Extensions;
using SharpCore.Utilities;

namespace MMS.DAO
{
    public class UserDAO
    {
        #region Fields

        private string connectionStringName;

        #endregion

        #region Constructors

        public UserDAO(string connectionStringName)
        {
            ValidationUtility.ValidateArgument("connectionStringName", connectionStringName);

            this.connectionStringName = connectionStringName;
        }

        #endregion

        #region Methods

        /// <summary>
        /// Saves a record to the User table.
        /// </summary>
        public void Insert(UserDTO user)
        {
            ValidationUtility.ValidateArgument("user", user);

            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", user.Id),
				new SqlParameter("@UserName", user.UserName),
				new SqlParameter("@Password", user.Password),
				new SqlParameter("@Type", user.Type),
				new SqlParameter("@Email", user.Email),
				new SqlParameter("@Active", user.Active),
				new SqlParameter("@Address", user.Address),
				new SqlParameter("@Name", user.Name),
				new SqlParameter("@Birthday", user.Birthday),
				new SqlParameter("@Phone", user.Phone),
				new SqlParameter("@Picture", SqlDbType.VarBinary, -1)
			};

            SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "sp_UserInsert", parameters);
        }

        /// <summary>
        /// Updates a record in the User table.
        /// </summary>
        public void Update(UserDTO user)
        {
            ValidationUtility.ValidateArgument("user", user);

            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", user.Id),
				new SqlParameter("@UserName", user.UserName),
				new SqlParameter("@Password", user.Password),
				new SqlParameter("@Type", user.Type),
				new SqlParameter("@Email", user.Email),
				new SqlParameter("@Active", user.Active),
				new SqlParameter("@Address", user.Address),
				new SqlParameter("@Name", user.Name),
				new SqlParameter("@Birthday", user.Birthday),
				new SqlParameter("@Phone", user.Phone),
				new SqlParameter("@Picture", SqlDbType.VarBinary, -1)
			};

            SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "sp_UserUpdate", parameters);
        }

        /// <summary>
        /// Deletes a record from the sp_User table by its primary key.
        /// </summary>
        public void Delete(string id)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", id)
			};

            SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "sp_UserDelete", parameters);
        }

        /// <summary>
        /// Selects a single record from the User table.
        /// </summary>
        public UserDTO Select(string id)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", id)
			};

            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "sp_UserSelect", parameters))
            {
                if (dataReader.Read())
                {
                    return MapDataReader(dataReader);
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// Selects a single record from the User table.
        /// </summary>
        public string SelectJson(string id)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", id)
			};

            return SqlClientUtility.ExecuteJson(connectionStringName, CommandType.StoredProcedure, "sp_UserSelect", parameters);
        }

        /// <summary>
        /// Selects all records from the User table.
        /// </summary>
        public List<UserDTO> SelectAll()
        {
            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "sp_UserSelectAll"))
            {
                List<UserDTO> userDTOList = new List<UserDTO>();
                while (dataReader.Read())
                {
                    UserDTO userDTO = MapDataReader(dataReader);
                    userDTOList.Add(userDTO);
                }

                return userDTOList;
            }
        }

        /// <summary>
        /// Selects all records from the User table.
        /// </summary>
        public string SelectAllJson()
        {
            return SqlClientUtility.ExecuteJson(connectionStringName, CommandType.StoredProcedure, "sp_UserSelectAll");
        }

        /// <summary>
        /// Creates a new instance of the UserDTO class and populates it with data from the specified SqlDataReader.
        /// </summary>
        private UserDTO MapDataReader(SqlDataReader dataReader)
        {
            UserDTO userDTO = new UserDTO();
            userDTO.Id = dataReader.GetString("Id", String.Empty);
            userDTO.UserName = dataReader.GetString("UserName", null);
            userDTO.Password = dataReader.GetString("Password", null);
            userDTO.Type = dataReader.GetString("Type", null);
            userDTO.Email = dataReader.GetString("Email", String.Empty);
            userDTO.Active = dataReader.GetBoolean("Active", false);
            userDTO.Address = dataReader.GetString("Address", null);
            userDTO.Name = dataReader.GetString("Name", null);
            userDTO.Birthday = dataReader.GetDateTime("Birthday", new DateTime(0));
            userDTO.Phone = dataReader.GetString("Phone", String.Empty);
            userDTO.Picture = dataReader.GetBytes("Picture", new byte[0]);

            return userDTO;
        }

        #endregion

        /// <summary>
        /// Selects a single record from the User table.
        /// </summary>
        public UserDTO SelectUserByUserName(string userName)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserName", userName)
            };

            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "dbo.sp_UserSelectByUserName", parameters))
            {
                if (dataReader.Read())
                {
                    return MapDataReader(dataReader);
                }
                else
                {
                    return null;
                }
            }
        }
    }
}
